<?xml version="1.0" encoding="utf-8"?>
<Vulcan xmlns="http://tempuri.org/vulcan2.xsd">
  <Connections>
    <Connection Name="TestConnection1" Type="OleDB"/>
  </Connections>
  <Packages>
    <Package Name="TestPackage1" Type="StoredProcedure">
      <Tasks>
        <ExecuteSQL Name="TestMerge1" ConnectionName="TestConnection1">
          <Query QueryType="Standard">
            <Body>
              MERGE [TestTargetTable1] AS TARGET
              USING (SELECT * FROM [TestTable1]) AS SOURCE
              ON (TARGET.[TestColumn1] = SOURCE.[TestColumn1])
              WHEN MATCHED
              AND
              (
              COALESCE(TARGET.[TestColumn1],'') &lt;&gt; COALESCE(SOURCE.[TestColumn1],'')
              OR
              COALESCE(TARGET.[TestColumn2],'') &lt;&gt; COALESCE(SOURCE.[TestColumn2],'')
              OR
              COALESCE(TARGET.[TestColumn3],'') &lt;&gt; COALESCE(SOURCE.[TestColumn3],'')
              OR
              COALESCE(TARGET.[TestColumn4],'') &lt;&gt; COALESCE(SOURCE.[TestColumn4],'')
              OR
              COALESCE(TARGET.[_scdFrom],'') &lt;&gt; COALESCE(SOURCE.[_scdFrom],'')
              OR
              COALESCE(TARGET.[_scdTo],'') &lt;&gt; COALESCE(SOURCE.[_scdTo],'')
              )
              THEN UPDATE SET
              TARGET.[TestColumn1] = SOURCE.[TestColumn1],TARGET.[TestColumn2] = SOURCE.[TestColumn2],TARGET.[TestColumn3] = SOURCE.[TestColumn3],TARGET.[TestColumn4] = SOURCE.[TestColumn4],TARGET.[_scdFrom] = SOURCE.[_scdFrom],TARGET.[_scdTo] = SOURCE.[_scdTo]
              WHEN NOT MATCHED BY TARGET THEN
              INSERT ([TestColumn1],
              [TestColumn2],
              [TestColumn3],
              [TestColumn4],
              [_scdFrom],
              [_scdTo]) VALUES (SOURCE.[TestColumn1],
              SOURCE.[TestColumn2],
              SOURCE.[TestColumn3],
              SOURCE.[TestColumn4],
              SOURCE.[_scdFrom],
              SOURCE.[_scdTo]);
            </Body>
          </Query>
        </ExecuteSQL>
      </Tasks>
    </Package>
    <Package Name="TestTable1" Type="Table">
      <Tasks>
        <ExecuteSQL Name="CreateTable" ConnectionName="TestConnection1">
          <Query QueryType="Standard">
            <Body>
              SET ANSI_NULLS ON
              SET QUOTED_IDENTIFIER ON
              GO

              -------------------------------------------------------------------
              IF EXISTS (SELECT * from sys.objects WHERE object_id = OBJECT_ID(N'[TestTargetTable1]') AND type IN (N'U'))
              DROP TABLE [TestTargetTable1]
              GO

              CREATE TABLE [TestTargetTable1]
              (
              -- Columns Definition
              [TestColumn1] int NOT NULL
              ,[TestColumn2] int NOT NULL
              ,[TestColumn3] int NOT NULL DEFAULT 1
              ,[TestColumn4] int NOT NULL
              ,[_scdFrom] datetime2(7) NOT NULL
              ,[_scdTo] datetime2(7) NOT NULL
              ,[VersionNumber] rowversion NOT NULL

              -- Constraints
              ,CONSTRAINT [PK] PRIMARY KEY CLUSTERED
              (
              [TestColumn1] Asc) WITH(PAD_INDEX = OFF,IGNORE_DUP_KEY = OFF) ON [PRIMARY]

              )
              ON [PRIMARY]
              WITH (DATA_COMPRESSION = NONE)
              GO

              -------------------------------------------------------------------
            </Body>
          </Query>
        </ExecuteSQL>
      </Tasks>
    </Package>
    <Package Name="TestTargetTable1" Type="Table">
      <Tasks>
        <ExecuteSQL Name="CreateTable" ConnectionName="TestConnection1">
          <Query QueryType="Standard">
            <Body>
              SET ANSI_NULLS ON
              SET QUOTED_IDENTIFIER ON
              GO

              -------------------------------------------------------------------
              IF EXISTS (SELECT * from sys.objects WHERE object_id = OBJECT_ID(N'[TestTable1]') AND type IN (N'U'))
              DROP TABLE [TestTable1]
              GO

              CREATE TABLE [TestTable1]
              (
              -- Columns Definition
              [TestColumn1] int NOT NULL
              ,[TestColumn2] int NOT NULL
              ,[VersionNumber] rowversion NOT NULL

              -- Constraints
              ,CONSTRAINT [PK] PRIMARY KEY CLUSTERED
              (
              [TestColumn1] Asc) WITH(PAD_INDEX = OFF,IGNORE_DUP_KEY = OFF) ON [PRIMARY]

              )
              ON [PRIMARY]
              WITH (DATA_COMPRESSION = NONE)
              GO

              -------------------------------------------------------------------
            </Body>
          </Query>
        </ExecuteSQL>
      </Tasks>
    </Package>
  </Packages>
</Vulcan>